package com.kvest.sqlitetest.datastorage.sqlitestorage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.kvest.sqlitetest.datastorage.sqlitestorage.table.*;

import java.util.Random;
import java.util.concurrent.TimeUnit;

/**
 * Created with IntelliJ IDEA.
 * User: Kvest
 * Date: 08.06.13     
 * Time: 21:46
 * To change this template use File | Settings | File Templates.
 */
public class TestHelper extends SQLiteOpenHelper {
    private static final String TAG = "KVEST_TAG";

    private static final String DATABASE_NAME = "test_db";
    private static final int DATABASE_VERSION = 3;

    public TestHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //create all tables
        sqLiteDatabase.execSQL(PersonTable.CREATE_TABLE_SQL);
        sqLiteDatabase.execSQL(FirmTable.CREATE_TABLE_SQL);
        sqLiteDatabase.execSQL(FirmPersonTable.CREATE_TABLE_SQL);
        sqLiteDatabase.execSQL(ProductTable.CREATE_TABLE_SQL);
        sqLiteDatabase.execSQL(FirmProductTable.CREATE_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        //При переходе версии от 1 к 2 в таблице PersonTable.TABLE_NAME появилось 1 новое поле PersonTable.COUNTRY_COLUMN
   //     if (oldVersion == 1 && newVersion == 2) {
            String CREATE_TEMP_TABLE_SQL = "CREATE TEMP TABLE \"tmp_" + PersonTable.TABLE_NAME +
                                            "\" AS SELECT * FROM \"" + PersonTable.TABLE_NAME + "\";";
            String COPY_SQL = "INSERT INTO \"" + PersonTable.TABLE_NAME + "\"(\"" + PersonTable._ID + "\",\"" +
                                PersonTable.FIRST_NAME_COLUMN + "\",\"" +
                                PersonTable.LAST_NAME_COLUMN + "\",\"" +
                                PersonTable.DAY_OF_BIRTH_COLUMN + "\",\"" +
                                PersonTable.MONTH_OF_BIRTH_COLUMN + "\",\"" +
                                PersonTable.YEAR_OF_BIRTH_COLUMN + "\") " +
                                " SELECT * from \"tmp_" + PersonTable.TABLE_NAME + "\";";
            String DROP_TEMP_TABLE_SQL = "DROP TABLE \"tmp_" + PersonTable.TABLE_NAME + "\";";

            sqLiteDatabase.beginTransaction();

            sqLiteDatabase.execSQL(CREATE_TEMP_TABLE_SQL);
            sqLiteDatabase.execSQL(PersonTable.DROP_TABLE_SQL);
            sqLiteDatabase.execSQL(PersonTable.CREATE_TABLE_SQL);
            sqLiteDatabase.execSQL(COPY_SQL);
            sqLiteDatabase.execSQL(DROP_TEMP_TABLE_SQL);

            sqLiteDatabase.setTransactionSuccessful();
            sqLiteDatabase.endTransaction();
  //      }

        Log.d(TAG, "onUpgrade " + oldVersion + " -> " + newVersion);
    }

    public void fill() {
        //first clear DB
        clear();

        //PersonTable
        addPerson("Иванов", "Иван", 11, 1, 2001);
        addPerson("Петров", "Петр", 12, 2, 2002);
        addPerson("Сидоров", "Сидр", 13, 3, 2003);
        addPerson("Петров", "Михаил", 19, 9, 2003);
        addPerson("Коваль", "Артур", 10, 5, 1995);

        //FirmTable
        addFirm("Рога копыта", "ан-2318", "Одесса", "Филатова", "22а");
        addFirm("Астра", "ан-2320", "Одесса", "Космонавтов", "2");
        addFirm("Рубин", "ен-2118", "Одесса", "Филатова", "20");
        addFirm("Чайная лавка", "ек-0934", "Одесса", "Малиновского", "105");

        //ProductTable
        addProduct("Рога", 7);
        addProduct("Копыта", 17);
        addProduct("Цветы", 27);
        addProduct("Драгметаллы", 37);
        addProduct("Чай", 38);
        addProduct("Кофе", 39);
        addProduct("Принтер", 40);
        addProduct("МФО", 41);
        for (int i = 50; i < 150; ++i) {
            addProduct("Продукт " + i, i);
        }

        //FirmPersonTable
        addFirmPerson(1, 5);
        addFirmPerson(1, 4);
        addFirmPerson(2, 3);
        addFirmPerson(3, 2);

        //FirmProductTable
        addFirmProduct(1, 1);
        addFirmProduct(1, 2);
        addFirmProduct(2, 3);
        addFirmProduct(3, 4);
        addFirmProduct(4, 5);
        addFirmProduct(4, 6);
    }

    public void clear() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            db.execSQL("DELETE FROM \"" + PersonTable.TABLE_NAME + "\" WHERE 1;");
            db.execSQL("DELETE FROM \"" + FirmTable.TABLE_NAME + "\" WHERE 1;");
            db.execSQL("DELETE FROM \"" + ProductTable.TABLE_NAME + "\" WHERE 1;");
            db.execSQL("DELETE FROM \"" + FirmPersonTable.TABLE_NAME + "\" WHERE 1;");
            db.execSQL("DELETE FROM \"" + FirmProductTable.TABLE_NAME + "\" WHERE 1;");
        } finally {
            db.close();
        }
    }

    public void printCountAll() {
        Log.d(TAG, PersonTable.TABLE_NAME + " count = " + getTableRowsCount(PersonTable.TABLE_NAME));
        Log.d(TAG, FirmTable.TABLE_NAME + " count = " + getTableRowsCount(FirmTable.TABLE_NAME));
        Log.d(TAG, ProductTable.TABLE_NAME + " count = " + getTableRowsCount(ProductTable.TABLE_NAME));
        Log.d(TAG, FirmPersonTable.TABLE_NAME + " count = " + getTableRowsCount(FirmPersonTable.TABLE_NAME));
        Log.d(TAG, FirmProductTable.TABLE_NAME + " count = " + getTableRowsCount(FirmProductTable.TABLE_NAME));
    }

    public void printAll() {
        printTable(PersonTable.TABLE_NAME);
        printTable(FirmTable.TABLE_NAME);
        printTable(ProductTable.TABLE_NAME);
        printTable(FirmPersonTable.TABLE_NAME);
        printTable(FirmProductTable.TABLE_NAME);
    }

    public int getTableRowsCount(String tableName) {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT count(*) FROM \"" + tableName + "\";", null);
            cursor.moveToFirst();
            return cursor.getInt(0);
        } finally {
            db.close();
        }
    }

    public Cursor getTableCursorWitoutClosingDB(String tableName) {
        SQLiteDatabase db = getReadableDatabase();
        return db.rawQuery("SELECT * FROM \"" + tableName + "\";", null);
    }

    public void printTable(String tableName) {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT * FROM \"" + tableName + "\";", null);
            Log.d(TAG, "Data from \"" + tableName + "\": ");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while (cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            cursor.close();
            Log.d(TAG, "--------------------------------------------");
        } finally {
            db.close();
        }
    }

    private static String getCursorHeader(Cursor cursor) {
        String result = "";
        for (int i = 0; i < cursor.getColumnCount(); ++i) {
            result += cursor.getColumnName(i) + "\t";
        }

        return result;
    }

    private static String getCursorData(Cursor cursor) {
        String result = "";
        for (int i = 0; i < cursor.getColumnCount(); ++i) {
            result += cursor.getString(i) + "\t";
        }

        return result;
    }

    public void printPersonTable() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.query(PersonTable.TABLE_NAME, new String[]{PersonTable._ID, PersonTable.FIRST_NAME_COLUMN,
                                    PersonTable.LAST_NAME_COLUMN, PersonTable.DAY_OF_BIRTH_COLUMN,
                                    PersonTable.MONTH_OF_BIRTH_COLUMN, PersonTable.YEAR_OF_BIRTH_COLUMN,
                                    PersonTable.COUNTRY_COLUMN},
                                    null, null, null, null, null);
            Log.d(TAG, "Data from " + PersonTable.TABLE_NAME + ": ");
            Log.d(TAG, "--------------------------------------------");
            while (cursor.moveToNext()) {
                Log.d(TAG, cursor.getInt(0) + "\t" + cursor.getString(1) + "\t" + cursor.getString(2) +
                           "\t" + cursor.getInt(3) + "\t" + cursor.getInt(4) + "\t" + cursor.getInt(5) + "\t" +
                            cursor.getString(6));
            }
            cursor.close();
            Log.d(TAG, "--------------------------------------------");
        } finally {
            db.close();
        }
    }

    public void addPerson(String firstName, String lastName, int dayOfBirth, int monthOfBirth, int yearOfBirth) {
        ContentValues values = new ContentValues(5);
        values.put(PersonTable.FIRST_NAME_COLUMN, firstName);
        values.put(PersonTable.LAST_NAME_COLUMN, lastName);
        values.put(PersonTable.DAY_OF_BIRTH_COLUMN, dayOfBirth);
        values.put(PersonTable.MONTH_OF_BIRTH_COLUMN, monthOfBirth);
        values.put(PersonTable.YEAR_OF_BIRTH_COLUMN, yearOfBirth);

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.insert(PersonTable.TABLE_NAME, null, values);
        } finally {
            db.close();
        }
    }

    public void addFirm(String name, String license,String city, String street, String building) {
        ContentValues values = new ContentValues(5);
        values.put(FirmTable.NAME_COLUMN, name);
        values.put(FirmTable.LICENSE_NUMBER_COLUMN, license);
        values.put(FirmTable.CITY_LOCATION_COLUMN, city);
        values.put(FirmTable.STREET_LOCATION_COLUMN, street);
        values.put(FirmTable.BUILDING_LOCATION_COLUMN, building);

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.insert(FirmTable.TABLE_NAME, null, values);
        } finally {
            db.close();
        }
    }

    public void addProduct(String name, int pid) {
        ContentValues values = new ContentValues(2);
        values.put(ProductTable.NAME_COLUMN, name);
        values.put(ProductTable.PID_COLUMN, pid);

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.insert(ProductTable.TABLE_NAME, null, values);
        } finally {
            db.close();
        }
    }

    public void addFirmPerson(int firmId, int personId) {
        ContentValues values = new ContentValues(2);
        values.put(FirmPersonTable.FIRM_ID_COLUMN, firmId);
        values.put(FirmPersonTable.PERSON_ID_COLUMN, personId);

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.insert(FirmPersonTable.TABLE_NAME, null, values);
        } finally {
            db.close();
        }
    }

    public void addFirmProduct(int firmId, int pid) {
        ContentValues values = new ContentValues(2);
        values.put(FirmProductTable.FIRM_ID_COLUMN, firmId);
        values.put(FirmProductTable.PRODUCT_ID_COLUMN, pid);

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.insert(FirmProductTable.TABLE_NAME, null, values);
        } finally {
            db.close();
        }
    }

    public void speedTest() {
        Log.d(TAG, "start speed test");
        clear();

        //Test insert requests
        long time;
        String SQL;
        SQLiteDatabase db = getWritableDatabase();
        try {
            SQL = "INSERT INTO \"" + PersonTable.TABLE_NAME + "\"(\"" + PersonTable.FIRST_NAME_COLUMN + "\",\"" +
                    PersonTable.LAST_NAME_COLUMN + "\",\"" + PersonTable.DAY_OF_BIRTH_COLUMN + "\",\"" +
                    PersonTable.MONTH_OF_BIRTH_COLUMN + "\",\"" + PersonTable.YEAR_OF_BIRTH_COLUMN  + "\") VALUES(" +
                    "\"test\",'test', 1, 2, 3);";
            time = System.currentTimeMillis();
            for (int i = 0; i < 10000; ++i) {
                db.execSQL(SQL);
            }
            Log.d(TAG, "Insert by SQL into PersonTable(20000 times) = " + (System.currentTimeMillis() - time));
            //Удаление работать будет только при первом запуске на чистой БД
            time = System.currentTimeMillis();
            for (int i = 1; i <= 10000; ++i) {
                SQL = "DELETE FROM \"" + PersonTable.TABLE_NAME + "\" WHERE _id=" + i + ";";
                db.execSQL(SQL);
            }
            Log.d(TAG, "DELETE by SQL from PersonTable = " + (System.currentTimeMillis() - time));
        } finally {
            db.close();
        }
        Log.d(TAG, PersonTable.TABLE_NAME + " count = " + getTableRowsCount(PersonTable.TABLE_NAME));

        clear();

        ContentValues values = new ContentValues(5);
        values.put(PersonTable.FIRST_NAME_COLUMN, "test");
        values.put(PersonTable.LAST_NAME_COLUMN, "test");
        values.put(PersonTable.DAY_OF_BIRTH_COLUMN, 1);
        values.put(PersonTable.MONTH_OF_BIRTH_COLUMN, 2);
        values.put(PersonTable.YEAR_OF_BIRTH_COLUMN, 3);
        db = getWritableDatabase();
        try {
            time = System.currentTimeMillis();
            for (int i = 0; i < 10000; ++i) {
                db.insert(PersonTable.TABLE_NAME, null, values);
            }
            Log.d(TAG, "Insert by Valuse into PersonTable(20000 times) = " + (System.currentTimeMillis() - time));
            //Удаление работать будет только при первом запуске на чистой БД
            time = System.currentTimeMillis();
            for (int i = 1; i <= 10000; ++i) {
                db.delete(PersonTable.TABLE_NAME, "_id=?", new String[]{Integer.toString(i + 10000)});
            }
            Log.d(TAG, "DELETE by SQL from PersonTable = " + (System.currentTimeMillis() - time));
        } finally {
            db.close();
        }
        Log.d(TAG, PersonTable.TABLE_NAME + " count = " + getTableRowsCount(PersonTable.TABLE_NAME));
        clear();
    }

    public void distinctTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT DISTINCT \"" + PersonTable.FIRST_NAME_COLUMN + "\" FROM \"" + PersonTable.TABLE_NAME + "\";", null);
            Log.d(TAG, "DISTINCT:");
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void limitTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT * FROM \"" + ProductTable.TABLE_NAME + "\" LIMIT 10;", null);
            Log.d(TAG, "LIMIT 10:");
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            cursor = db.rawQuery("SELECT * FROM \"" + ProductTable.TABLE_NAME + "\" LIMIT 5,10;", null);
            Log.d(TAG, "LIMIT 5,10:");
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void groupByTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT \"" + PersonTable.FIRST_NAME_COLUMN + "\", count(\"" + PersonTable.FIRST_NAME_COLUMN +
                                        "\") FROM \"" + PersonTable.TABLE_NAME + "\" GROUP BY \"" + PersonTable.FIRST_NAME_COLUMN + "\";", null);
            Log.d(TAG, "GROUP BY:");
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void havingTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT \"" + PersonTable.FIRST_NAME_COLUMN + "\", count(\"" + PersonTable.FIRST_NAME_COLUMN +
                    "\") as \"rows_count\" FROM \"" + PersonTable.TABLE_NAME + "\" GROUP BY \"" + PersonTable.FIRST_NAME_COLUMN + "\" " +
                    "HAVING \"rows_count\" > 1;", null);
            Log.d(TAG, "HAVING:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void stringCaseTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            //UPPER and LOWER don't work with russian letters !!!!!!!!!
            Cursor cursor = db.rawQuery("SELECT UPPER(\"" + PersonTable.FIRST_NAME_COLUMN + "\"), LOWER(\"" +
                                        PersonTable.LAST_NAME_COLUMN + "\"), SUBSTR(\"" +
                                        PersonTable.FIRST_NAME_COLUMN + "\",2,3) FROM \"" + PersonTable.TABLE_NAME + "\";", null);
            Log.d(TAG, "UPPER\\LOWER\\SUBSTR(2,3):");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void likeCaseTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            Cursor cursor = db.rawQuery("SELECT * FROM \"" + PersonTable.TABLE_NAME + "\" WHERE \"" +
                                PersonTable.FIRST_NAME_COLUMN + "\" LIKE \"%ов\";", null);
            Log.d(TAG, "LIKE:");
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void uniqueNotNullTest() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            db.execSQL("DROP TABLE IF EXISTS \"test\";");
            db.execSQL("CREATE TABLE \"test\"(\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT, \"name\" TEXT UNIQUE NOT NULL, \"cnt\" INTEGER UNIQUE NOT NULL DEFAULT 5);");
            db.execSQL("INSERT INTO \"test\"(\"name\", \"cnt\") VALUES (\"Kvest\", 2);");

            //INSERT INTO "test"("name", "cnt") VALUES ("Kvest", 3);
            //Запрос не проходит т.к. поле "name" уникальным должно быть

            //INSERT INTO "test"("name", "cnt") VALUES ("Kvest5", 2);
            //Запрос не проходит т.к. поле "cnt" уникальным должно быть

            //INSERT INTO "test"("cnt") VALUES (27);
            //Запрос не проходит т.к. поле "name" не должно быть нулевым

            db.execSQL("INSERT INTO \"test\"(\"name\", \"cnt\") VALUES (\"Kvest1\", 22);");
            db.execSQL("INSERT INTO \"test\"(\"name\", \"cnt\") VALUES (\"Kvest2\", 7);");
            db.execSQL("INSERT INTO \"test\"(\"name\") VALUES (\"Kvest3\");");

            //INSERT INTO "test"("name") VALUES ("Kvest4");
            //Запрос не проходит т.к. поле "cnt" не должно быть нулевым и уникальным, а с дефолтным значением запись уже вставили

            //Show data in test table
            Cursor cursor = db.rawQuery("SELECT * FROM \"test\";", null);
            Log.d(TAG, "TEST:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void nestedTest() {
        SQLiteDatabase db = getReadableDatabase();
        try {
            //Выбираем фирмы, у которых нет работников
            Cursor cursor = db.rawQuery("SELECT * FROM \"firm\" WHERE \"_id\" NOT IN (SELECT \"firm_id\" FROM \"firm_person\");", null);
            Log.d(TAG, "NESTED:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void indexTest() {
        Log.d(TAG, "start index test");
        SQLiteDatabase db = getWritableDatabase();
        try {
            //Змеряем без индексов
            db.execSQL("DROP TABLE IF EXISTS \"test\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_id_index\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_name_index\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_composite_index\";");
            db.execSQL("CREATE TABLE \"test\"(\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT, \"person_id\" INTEGER NOT NULL, \"person_name\" TEXT NOT NULL);");
            db.beginTransaction();
            try{
                for (int i = 0; i < 100000; ++i) {
                    db.execSQL("INSERT INTO \"test\"(\"person_id\", \"person_name\") VALUES(" + i + ", \"человек " + i + "\");");
                }
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }

            long time = System.currentTimeMillis();
            Cursor cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_id\"=99777;", null);
            Log.d(TAG, "time by person_id=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITHOUT INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            time = System.currentTimeMillis();
            cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_name\"=\"человек 99777\";", null);
            Log.d(TAG, "time by person_name=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITHOUT INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            time = System.currentTimeMillis();
            cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_name\"=\"человек 99777\" AND \"person_id\"=99777;", null);
            Log.d(TAG, "time by person_name and person_id=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITHOUT INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //Змеряем время с индексами
            db.execSQL("DROP TABLE IF EXISTS \"test\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_id_index\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_name_index\";");
            db.execSQL("DROP INDEX IF EXISTS \"person_composite_index\";");
            db.execSQL("CREATE TABLE \"test\"(\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT, \"person_id\" INTEGER NOT NULL, \"person_name\" TEXT NOT NULL);");
            db.execSQL("CREATE INDEX IF NOT EXISTS \"person_id_index\" ON \"test\"(\"person_id\");");
            //Запрос ничего не сделает, т.к. индекс уже существует
            db.execSQL("CREATE INDEX IF NOT EXISTS \"person_id_index\" ON \"test\"(\"person_id\");");
            db.execSQL("CREATE INDEX IF NOT EXISTS \"person_name_index\" ON \"test\"(\"person_name\");");
            db.execSQL("CREATE INDEX IF NOT EXISTS \"person_composite_index\" ON \"test\"(\"person_id\", \"person_name\");");
            db.beginTransaction();
            try{
                for (int i = 0; i < 100000; ++i) {
                    db.execSQL("INSERT INTO \"test\"(\"person_id\", \"person_name\") VALUES(" + i + ", \"человек " + i + "\");");
                }
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }

            time = System.currentTimeMillis();
            cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_id\"=99777;", null);
            Log.d(TAG, "time by person_id_index=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITH INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            time = System.currentTimeMillis();
            cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_name\"=\"человек 99777\";", null);
            Log.d(TAG, "time by person_name_index=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITH INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            time = System.currentTimeMillis();
            cursor = db.rawQuery("SELECT * FROM \"test\" WHERE \"person_name\"=\"человек 99777\" AND \"person_id\"=99777;", null);
            Log.d(TAG, "time by person_composite_index=" + (System.currentTimeMillis() - time));
            Log.d(TAG, "WITH INDEX:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void joinTest() {
        Log.d(TAG, "start index test");
        SQLiteDatabase db = getReadableDatabase();
        try {
            //Whole data
            String SQL = "SELECT * " +
                         "FROM \"firm\" as FM INNER JOIN \"firm_person\" as FP ON FM.\"_id\" = FP.\"firm_id\" " +
                                             "INNER JOIN \"person\" as PS ON FP.\"person_id\"=PS.\"_id\" " +
                                             "INNER JOIN \"firm_product\" as FPD ON FM.\"_id\" = FPD.\"firm_id\" "+
                                             "INNER JOIN \"product\" as PD ON PD.\"_id\" = FPD.\"product_id\";";
            Cursor cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "Whole data:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            SQL = "SELECT PS.\"first_name\", PS.\"last_name\", PD.\"name\" as \"product_name\" " +
                    "FROM \"firm\" as FM INNER JOIN \"firm_person\" as FP ON FM.\"_id\" = FP.\"firm_id\" " +
                    "INNER JOIN \"person\" as PS ON FP.\"person_id\"=PS.\"_id\" " +
                    "INNER JOIN \"firm_product\" as FPD ON FM.\"_id\" = FPD.\"firm_id\" "+
                    "INNER JOIN \"product\" as PD ON PD.\"_id\" = FPD.\"product_id\";";
            cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "Whole data:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //products, produced by Коваль
            SQL = "SELECT PS.\"first_name\", PS.\"last_name\", PD.\"name\" as \"product_name\" " +
                    "FROM \"firm\" as FM INNER JOIN \"firm_person\" as FP ON FM.\"_id\" = FP.\"firm_id\" " +
                    "INNER JOIN \"person\" as PS ON FP.\"person_id\"=PS.\"_id\" " +
                    "INNER JOIN \"firm_product\" as FPD ON FM.\"_id\" = FPD.\"firm_id\" "+
                    "INNER JOIN \"product\" as PD ON PD.\"_id\" = FPD.\"product_id\" " +
                    "WHERE PS.\"first_name\"=\"Коваль\";";
            cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "products, produced by Коваль:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //Left Join
            SQL = "SELECT * FROM \"firm\" as FM LEFT JOIN \"firm_person\" as FP ON FM.\"_id\" = FP.\"firm_id\" LEFT JOIN \"person\" as PS ON FP.\"person_id\"=PS.\"_id\";";
            cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "Left Join:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }

        //Compare "Where =" with "join" with "nested requests"
        db = getWritableDatabase();
        try {
            //create tables for test and fill them
            db.execSQL("DROP TABLE IF EXISTS \"test1\";");
            db.execSQL("DROP TABLE IF EXISTS \"test2\";");
            db.execSQL("DROP TABLE IF EXISTS \"test3\";");
            db.execSQL("CREATE TABLE \"test1\"(\"id\" INTEGER PRIMARY KEY , \"str\" TEXT);");
            db.execSQL("CREATE TABLE \"test3\"(\"id\" INTEGER PRIMARY KEY , \"str\" TEXT);");
            db.execSQL("CREATE TABLE \"test2\"(\"id1\" INTEGER , \"id2\" INTEGER);");
            for (int i = 0; i < 1000; ++i) {
                ContentValues values1 = new ContentValues(2);
                values1.put("id", Integer.toString(i));
                values1.put("str", "str " + Integer.toString(i));
                db.insert("test1", null, values1);

                ContentValues values2 = new ContentValues(2);
                values2.put("id", Integer.toString(i));
                values2.put("str", "str " + Integer.toString(i));
                db.insert("test3", null, values2);

                ContentValues values3 = new ContentValues(2);
                values3.put("id1", Integer.toString(i));
                values3.put("id2", Integer.toString(1000 - i - 1));
                db.insert("test2", null, values3);
            }

            Log.d(TAG, "Start compare");

            //Compare
            String SQL = "SELECT \"test1\".\"str\", \"test3\".\"str\" " +
                         "FROM \"test1\", \"test2\", \"test3\" " +
                         "WHERE \"test1\".\"id\"=\"test2\".\"id1\" AND \"test2\".\"id2\"=\"test3\".\"id\" AND " +
                         "(\"test1\".\"id\"=7 OR \"test1\".\"id\"=877);";
            long time = System.currentTimeMillis();
            for (int i = 0; i < 1000; ++i) {
                db.rawQuery(SQL, null).close();
            }
            Log.d(TAG, "USING WHERE=" + (System.currentTimeMillis() - time));
            Cursor cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "DATA:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            SQL = "SELECT \"test1\".\"str\", \"test3\".\"str\" " +
                  "FROM \"test1\" INNER JOIN \"test2\" ON \"test1\".\"id\"=\"test2\".\"id1\" " +
                                 "INNER JOIN \"test3\" ON \"test2\".\"id2\"=\"test3\".\"id\" " +
                  "WHERE \"test1\".\"id\"=7 OR \"test1\".\"id\"=877;";
            time = System.currentTimeMillis();
            for (int i = 0; i < 1000; ++i) {
                db.rawQuery(SQL, null).close();
            }
            Log.d(TAG, "USING JOIN=" + (System.currentTimeMillis() - time));
            cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "DATA:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            SQL =  "SELECT \"str\" FROM \"test3\" WHERE \"id\" in" +
                    "(SELECT \"id2\" FROM \"test2\" WHERE \"id1\" in (SELECT \"id\" FROM \"test1\" WHERE \"test1\".\"id\"=7 OR \"test1\".\"id\"=877));";
            time = System.currentTimeMillis();
            for (int i = 0; i < 1000; ++i) {
                db.rawQuery(SQL, null).close();
            }
            Log.d(TAG, "USING NESTED REQUESTS=" + (System.currentTimeMillis() - time));
            cursor = db.rawQuery(SQL, null);
            Log.d(TAG, "USING NESTED REQUESTS:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void foreignKeyTest() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            //ВАЖНО!!!!!
            //При использовании foreign Key надо делать PRAGMA foreign_keys=ON;, иначе не будет работать.
            //Или вызвать setForeignKeyConstraintsEnabled(boolean enable) у SQLiteDatabase, но не тестировал
            //Это НАВЕРНО(не тестировал) лучше делать в переопределенном методе getWritableDatabase хэлпера. getReadableDatabase можно не переопределять
            String SQL = "PRAGMA foreign_keys=ON;";
            db.execSQL(SQL);

            Cursor cursor = db.rawQuery("PRAGMA foreign_keys;", null);
            Log.d(TAG, "PRAGMA foreign_keys:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();


            SQL = "DROP TABLE IF EXISTS \"t1\";";
            db.execSQL(SQL);
            SQL = "DROP TABLE IF EXISTS \"t2\";";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t2\"(_id INTEGER PRIMARY KEY AUTOINCREMENT, \"str\" TEXT);";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t1\"(_id INTEGER PRIMARY KEY AUTOINCREMENT, \"tid2\" INTEGER CONSTRAINT \"test\" REFERENCES \"t2\"(\"_id\") ON DELETE SET NULL ON UPDATE CASCADE MATCH FULL);";
            db.execSQL(SQL);

            for (int i = 0; i < 10; ++i) {
                SQL = "INSERT INTO \"t2\" VALUES(" + i + ", \"str " + i + "\");";
                db.execSQL(SQL);
                SQL = "INSERT INTO \"t1\" VALUES(" + (9 - i) + ", " + i + ");";
                db.execSQL(SQL);
            }

            //print tables
            cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            cursor = db.rawQuery("SELECT * FROM \"t2\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //CASCADE UPDATE and DELETE some lines
            SQL = "UPDATE \"t2\" SET \"_id\"=15 WHERE \"_id\"=5;";
            db.execSQL(SQL);
            SQL = "UPDATE \"t2\" SET \"_id\"=17 WHERE \"_id\"=7;";
            db.execSQL(SQL);
            SQL = "DELETE FROM \"t2\" WHERE \"_id\"=3 OR \"_id\"=6;";
            db.execSQL(SQL);

            //print tables
            cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            cursor = db.rawQuery("SELECT * FROM \"t2\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void triggerTest() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            String SQL = "DROP TABLE IF EXISTS \"t1\";";
            db.execSQL(SQL);
            SQL = "DROP TABLE IF EXISTS \"t2\";";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t1\"(\"_id\" INTEGER PRIMARY KEY, \"val1\" TEXT, \"val2\" TEXT);";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t2\"(\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT, \"action\" TEXT, \"old_val\" TEXT, \"new_val\" TEXT);";
            db.execSQL(SQL);

            //Triggers
            SQL = "CREATE TRIGGER IF NOT EXISTS \"before_insert\" BEFORE INSERT ON \"t1\" WHEN new.\"_id\"=1 BEGIN " +
                  "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"before_insert\",\"\",new.\"val1\"); " +
                  "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"after_insert\" AFTER INSERT ON \"t1\" WHEN new.\"_id\"=1 BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"after_insert\",\"\",new.\"val1\"); " +
                    "UPDATE \"t1\" SET \"val2\"=new.\"val1\";" +
                    " END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"after_delete\" AFTER DELETE ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"after_delete\",old.\"val1\",\"\"); " +
                    "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"before_delete\" BEFORE DELETE ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"before_delete\",old.\"val1\",\"\"); " +
                    "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"after_update\" AFTER UPDATE ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"after_update\",old.\"val1\",new.\"val1\"); " +
                    "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"before_update\" BEFORE UPDATE ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"before_update\",old.\"val1\",new.\"val1\"); " +
                    "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"after_updateof\" AFTER UPDATE OF \"val1\" ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"after_updateof\",old.\"val1\",new.\"val1\"); " +
                    "END;";
            db.execSQL(SQL);
            SQL = "CREATE TRIGGER IF NOT EXISTS \"before_updateof\" BEFORE UPDATE OF \"val1\" ON \"t1\" BEGIN " +
                    "INSERT INTO \"t2\"(\"action\", \"old_val\", \"new_val\") VALUES(\"before_updateof\",old.\"val1\",new.\"val1\"); " +
                    "END;";
            db.execSQL(SQL);

            //Do changes
            db.execSQL("INSERT INTO \"t1\" VALUES(1, \"val 1\", \"val2 1\");");
            db.execSQL("INSERT INTO \"t1\" VALUES(2, \"val 2\", \"val2 2\");");
            db.execSQL("INSERT INTO \"t1\" VALUES(3, \"val 3\", \"val2 3\");");
            db.execSQL("INSERT INTO \"t1\" VALUES(4, \"val 4\", \"val2 4\");");
            db.execSQL("INSERT INTO \"t1\" VALUES(5, \"val 5\", \"val2 5\");");

            db.execSQL("DELETE FROM \"t1\" WHERE \"_id\"=5;");
            db.execSQL("UPDATE \"t1\" SET \"val1\"=\"qwe\" WHERE \"_id\"=4;");
            db.execSQL("UPDATE \"t1\" SET \"val2\"=\"qwe2\" WHERE \"_id\"=3;");
            db.execSQL("UPDATE \"t1\" SET \"val1\"=\"qwe\",\"val2\"=\"qwe2\" WHERE \"_id\"=2;");

            //print tables
            Cursor cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
            cursor = db.rawQuery("SELECT * FROM \"t2\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void openNotOpenSpeedTest() {
        Log.d(TAG, "start openNotOpenSpeedTest");
        SQLiteDatabase db = getReadableDatabase();
        try {
            long time = System.currentTimeMillis();

            for (int i = 0; i < 1000; ++i) {
                Cursor cursor = db.rawQuery("SELECT * FROM \"" + PersonTable.TABLE_NAME + "\";", null);
                cursor.close();
            }

            Log.d(TAG, "if not reopen=" + (System.currentTimeMillis() - time));
        } finally {
            db.close();
        }

        long time = System.currentTimeMillis();
        for (int i = 0; i < 1000; ++i) {
            db = getReadableDatabase();

            Cursor cursor = db.rawQuery("SELECT * FROM \"" + PersonTable.TABLE_NAME + "\";", null);
            cursor.close();

            db.close();
        }
        Log.d(TAG, "if reopen=" + (System.currentTimeMillis() - time));
    }

    public void transactionTest() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            String SQL = "DROP TABLE IF EXISTS \"t1\";";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t1\"(\"_id\" INTEGER PRIMARY KEY, \"val1\" TEXT);";
            db.execSQL(SQL);

            //normal transaction
            db.beginTransaction();
            try {
                for (int i = 0; i < 10; ++i) {
                    SQL = "INSERT INTO \"t1\" VALUES(" + i + ", \"str " + i +"\");";
                    db.execSQL(SQL);
                }
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }

            Cursor cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //transaction without setTransactionSuccessful() calling
            db.beginTransaction();
            try {
                for (int i = 10; i < 15; ++i) {
                    SQL = "INSERT INTO \"t1\" VALUES(" + i + ", \"str " + i +"\");";
                    db.execSQL(SQL);
                }
            } finally {
                db.endTransaction();
            }

            cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();

            //transaction with exception
            db.beginTransaction();
            try {
                for (int i = 8; i < 15; ++i) {
                    SQL = "INSERT INTO \"t1\" VALUES(" + i + ", \"str " + i +"\");";
                        db.execSQL(SQL);
                }
                db.setTransactionSuccessful();
            } catch (SQLException sqlExeption) {
                Log.d(TAG, "SQLException=" + sqlExeption.getMessage());
            } finally {
                db.endTransaction();
            }

            cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "t1:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void limitVsOffsetTest() {
/*        SELECT * FROM t1 ORDER BY _id DESC LIMIT 10;
        --SELECT * FROM t1 ORDER BY _id DESC LIMIT 10;
        --SELECT * FROM t1 LIMIT 10 OFFSET (SELECT count(*) FROM t1) - 10;


        Order:
        100000: 30-40мс
        1000:   25-40мс


        Order no index:
        100000: 300-310мс
        1000:   60-70мс


        offset:
        100000: 75-90мс
        1000:   40-50мс

                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=3307778
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=2486222
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=1605140
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=1749849
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=1590258
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=2283014
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=1658507
                [SELECT * FROM "t1" ORDER BY "val2" DESC LIMIT 10]=1871979
        avg=2069093

                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=2646839
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1552285
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1627718
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1514312
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=2303028
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1618482
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1532785
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1523548
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1442984
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1642087
                [SELECT * FROM "t1" ORDER BY "_id" DESC LIMIT 10]=1426563
        avg=1711875

                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=3435041
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1743178
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=3283148
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=2016689
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1893019
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1691349
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=2405658
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1598982
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=2396420
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1944335
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=2056201
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1806809
                [select * from "t1" limit 10 offset (select count(*) from "t1") - 10;]=1600008
        avg=2143910
            */

        SQLiteDatabase db = getWritableDatabase();
        try {
            db.execSQL("PRAGMA automatic_index = false;");
            Cursor cursor;

            //create table for test
//            String SQL = "DROP TABLE IF EXISTS \"t1\";";
//            db.execSQL(SQL);
//            SQL = "CREATE TABLE \"t1\"(\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT, \"val1\" TEXT, \"val2\" INTEGER);";
//            db.execSQL(SQL);
//            int count = 100000;
//            Random r = new Random(System.currentTimeMillis());
//            for (int i = 0; i < count; ++i) {
//                SQL = "INSERT INTO \"t1\"(\"val1\", \"val2\") VALUES (\"str " + i + "\", " + r.nextInt()/*((i + 17) % count)*/ + ")";
//                db.execSQL(SQL);
//            }
//            Cursor cursor = db.rawQuery("SELECT count(*) FROM \"t1\";", null);
//            cursor.moveToNext();
//            Log.d(TAG, "fill finish=" + cursor.getInt(0));
//            cursor.close();


            long start = System.nanoTime();
            //String sql = "SELECT * FROM \"t1\" ORDER BY \"_id\" DESC LIMIT 10";
            String sql = "select * from \"t1\" limit 10 offset (select count(*) from \"t1\") - 10;";
            cursor = db.rawQuery(sql, null);
            Log.d(TAG, "[" + sql + "]=" + (System.nanoTime() - start));
//            Log.d(TAG, "--------------------------------------------");
//            while(cursor.moveToNext()) {
//                Log.d(TAG, getCursorData(cursor));
//            }
//            Log.d(TAG, "--------------------------------------------");
            cursor.close();

        } finally {
            db.close();
        }
    }

    public void insertOrUpdateTest() {
        SQLiteDatabase db = getWritableDatabase();
        try {
            String SQL = "DROP TABLE IF EXISTS \"t1\";";
            db.execSQL(SQL);
            SQL = "CREATE TABLE \"t1\"(\"_id\" INTEGER PRIMARY KEY, \"val1\" TEXT);";
            db.execSQL(SQL);

            for (int i = 0; i < 10; ++i) {
                SQL = "INSERT INTO \"t1\" VALUES (" + i + ", \"str " + i + "\")";
                db.execSQL(SQL);
            }

            SQL = "INSERT OR REPLACE INTO \"t1\" VALUES (0, \"new str 0\")";
            db.execSQL(SQL);
            SQL = "INSERT OR IGNORE INTO \"t1\" VALUES (1, \"new str 1\")";
            db.execSQL(SQL);

            SQL = "UPDATE OR REPLACE \"t1\" SET \"_id\"=8, \"val1\"=\"This is old 2\" WHERE \"_id\"=2;";
            db.execSQL(SQL);
            SQL = "UPDATE OR IGNORE \"t1\" SET \"_id\"=9, \"val1\"=\"This is old 3\" WHERE \"_id\"=3;";
            db.execSQL(SQL);

            SQL = "UPDATE OR REPLACE \"t1\" SET \"_id\"=4, \"val1\"=\"This is self replace 4\" WHERE \"_id\"=4;";
            db.execSQL(SQL);
            SQL = "UPDATE OR IGNORE \"t1\" SET \"_id\"=5, \"val1\"=\"This is self replace 5\" WHERE \"_id\"=5;";
            db.execSQL(SQL);

            Cursor cursor = db.rawQuery("SELECT * FROM \"t1\"", null);
            Log.d(TAG, "insertOrUpdateTest:");
            Log.d(TAG, "--------------------------------------------");
            Log.d(TAG, getCursorHeader(cursor));
            Log.d(TAG, "--------------------------------------------");
            while(cursor.moveToNext()) {
                Log.d(TAG, getCursorData(cursor));
            }
            Log.d(TAG, "--------------------------------------------");
            cursor.close();
        } finally {
            db.close();
        }
    }

    public void fillFirm(int offset, int count) {
        SQLiteDatabase db = getWritableDatabase();
        try {
            db.beginTransaction();
            try {
                for (int i = 0; i < count; ++i) {
                    ContentValues values = new ContentValues(5);
                    values.put(FirmTable.NAME_COLUMN, "firm " + (offset + i));
                    values.put(FirmTable.LICENSE_NUMBER_COLUMN, (offset + i) + "-" + (offset + i) + "-AA");
                    values.put(FirmTable.CITY_LOCATION_COLUMN, "test");
                    values.put(FirmTable.STREET_LOCATION_COLUMN, "street " + (offset + i));
                    values.put(FirmTable.BUILDING_LOCATION_COLUMN, Integer.toString((offset + i)));
                    db.insert(FirmTable.TABLE_NAME, null, values);
                }

                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        } finally {
            db.close();
        }
    }

    public void getFirm(String name) {
        //This is test stub
        Log.d(TAG, name + "=" + getTableRowsCount(FirmTable.TABLE_NAME));
    }
}
